package com.boxtelecom.db;

import com.boxtelecom.users.FacebookUser;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class UserDBAdapter {

	private static String DATABASE_TABLE_USER = "user";
	private static String DATABASE_TABLE_FRIEND = "friends";
	private static final int DATABASE_VERSION = 1;
	
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_BIRTHDAY = "birthday";
	private static final String KEY_SIGNE = "signe";
	private static final String KEY_FJID = "fjid";
	private static final String KEY_PICTURE = "picture";
	private static final String KEY_TIME = "time";
	
	private SQLiteDatabase _db;
	private final Context _context;
	
	private MyCircleDBOpenHelper _dbHelper;
	
		public UserDBAdapter(Context aContext, String db) {
		this._context = aContext;
		_dbHelper = new MyCircleDBOpenHelper(aContext, db, null, DATABASE_VERSION);
	}

	/**
	 * open the table 'user'
	 */
	public void open() {
		try {
			_db = _dbHelper.getWritableDatabase();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			_db = _dbHelper.getReadableDatabase();
		}
	}
	
	/**
	 * close the table 'user'
	 */
	public void close(){
		_db.close();
	}
	
	/**
	 * check if the table 'user'
	 * @return true if table exists, else false
	 */
	public boolean isTable(){
		Cursor cursor = _db.rawQuery("SELECT * FROM " + DATABASE_TABLE_USER, null);
		int count = cursor.getCount();
		cursor.close();
		// Log.d("db", String.valueOf(count));
		if(count>0){
			return true;
		} else {
			return false;
		}
	}

	/**
	 * insert an user into the database 'user'
	 * @param user the user to insert
	 * @return false if the table does not exist, else true
	 */
	public boolean insertUser(FacebookUser user){		
		if(isTable()){
			return false;
		} else {
			ContentValues newFriend = new ContentValues();
			newFriend.put(KEY_ID, user.getId());
			newFriend.put(KEY_NAME, user.getName());
			newFriend.put(KEY_BIRTHDAY, user.getBirth());
			newFriend.put(KEY_SIGNE, user.getSigneAstro());
			newFriend.put(KEY_FJID, user.getFjid());
			newFriend.put(KEY_PICTURE, user.getPicturePath());
			newFriend.put(KEY_TIME, user.getUpdatedTime());
			
			return _db.insert(DATABASE_TABLE_USER, null, newFriend) > 0;
		}
	}
	
	/**
	 * delete all of the rows of the database
	 * @return true if it has been successfull, else false
	 */
	public boolean deleteAll(){
		return _db.delete(DATABASE_TABLE_USER, null, null) > 0;
	}
	
	/**
	 * print all the rows of the table
	 */
	public void selectAll(){
		Cursor cursor = _db.rawQuery("select * from user", null);
		if(cursor.moveToFirst()){
			do{
				int idIndex = cursor.getColumnIndex(KEY_ID);
				String id = cursor.getString(idIndex);;
				
				int nameIndex = cursor.getColumnIndex(KEY_NAME);
				String name = cursor.getString(nameIndex);
				
				int birthdayIndex = cursor.getColumnIndex(KEY_BIRTHDAY);
				String birthday = cursor.getString(birthdayIndex);
				
				int signeIndex = cursor.getColumnIndex(KEY_SIGNE);
				String signe = cursor.getString(signeIndex);
				
				int fjidIndex = cursor.getColumnIndex(KEY_FJID);
				String fjid = cursor.getString(fjidIndex);
				
				int pictureIndex = cursor.getColumnIndex(KEY_PICTURE);
				String picture = cursor.getString(pictureIndex);
				
				int timeIndex = cursor.getColumnIndex(KEY_TIME);
				String time = cursor.getString(timeIndex);
				
				Log.d("db", id + " " + name + " " + birthday + " " + signe + " " + fjid + " " + picture + " " + time);
			}while(cursor.moveToNext());
		} else {
			Log.d("db", "table user empty");
		}
		cursor.close();		
	}

	/**
	 * access to the table user to iterate it
	 * @return a cursor containing all the values
	 */
	private Cursor getUserCursor(){
		return _db.query(DATABASE_TABLE_USER, new String[]{KEY_ID, KEY_NAME, KEY_BIRTHDAY, KEY_SIGNE, KEY_FJID, KEY_PICTURE, KEY_TIME}, null, null, null, null, null);
	}
	
	/**
	 * get the information of the user stored in the table 'user'
	 * @return the user stored
	 */
	public FacebookUser getFacebookUser(){
		Cursor cursor = getUserCursor();
		cursor.moveToFirst();
		
		int idIndex = cursor.getColumnIndex(KEY_ID);
		String id = cursor.getString(idIndex);;
		
		int nameIndex = cursor.getColumnIndex(KEY_NAME);
		String name = cursor.getString(nameIndex);
		
		int birthdayIndex = cursor.getColumnIndex(KEY_BIRTHDAY);
		String birthday = cursor.getString(birthdayIndex);
		
		int signeIndex = cursor.getColumnIndex(KEY_SIGNE);
		String signe = cursor.getString(signeIndex);
		
		int fjidIndex = cursor.getColumnIndex(KEY_FJID);
		String fjid = cursor.getString(fjidIndex);
		
		int pictureIndex = cursor.getColumnIndex(KEY_PICTURE);
		String picture = cursor.getString(pictureIndex);
		
		int timeIndex = cursor.getColumnIndex(KEY_TIME);
		String time = cursor.getString(timeIndex);
		
		FacebookUser user = new FacebookUser(id, name, birthday, signe, fjid);
		user.setPicturePath(picture);
		user.setUpdatedTime(time);
		
		cursor.close();
		
		return user;
	}
	
	private static class MyCircleDBOpenHelper extends SQLiteOpenHelper {

		private static final String DATABASE_CREATE_TABLE_USER = "create table " + DATABASE_TABLE_USER + " (" +
				KEY_ID + " VARCHAR(20), " + KEY_NAME + " VARCHAR(20), " + KEY_BIRTHDAY + " VARCHAR(20), " +
				KEY_SIGNE + " VARCHAR(20), " + KEY_FJID + " VARCHAR(20), " + KEY_PICTURE + " VARCHAR(20), " + KEY_TIME + " VARCHAR(20));";
		
		private static final String DATABASE_CREATE_TABLE_FRIEND = "create table " + DATABASE_TABLE_FRIEND + " (" +
		KEY_ID + " VARCHAR(20), " + KEY_NAME + " VARCHAR(20), " + KEY_BIRTHDAY + " VARCHAR(20), " +
		KEY_SIGNE + " VARCHAR(20), " + KEY_FJID + " VARCHAR(20), " + KEY_PICTURE + " VARCHAR(20), " + KEY_TIME + " VARCHAR(20));";

		
		public MyCircleDBOpenHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(DATABASE_CREATE_TABLE_USER);
			db.execSQL(DATABASE_CREATE_TABLE_FRIEND);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_USER);
			onCreate(db);
		}

		@Override
		public void onOpen(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			super.onOpen(db);
		}		
	}	
}
